from sqlalchemy import create_engine
HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'ssql'
USERNAME ='root'
PASSWORD = 'root'

#engine=create_engine('mysql+pymysql://root:qwe123@localhost:3306/test',echo=True)
DB_URI = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(USERNAME,PASSWORD,HOSTNAME,PORT,DATABASE)
print(DB_URI)
engine = create_engine(DB_URI)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base(engine)

from sqlalchemy import Column,Integer,String,Text,Boolean,Date,DateTime,Float,ForeignKey,Table
from datetime import datetime,date
from sqlalchemy.orm import relationship

test = Table('test',Base.metadata,
	Column('id',Integer,primary_key=True),
	Column('name',String(100)))


stu_course = Table('stu_course',Base.metadata,
	Column('s_id',Integer,ForeignKey('students.s_id'),primary_key=True),
	Column('c_id',Integer,ForeignKey('course.c_id'),primary_key=True))


class Students(Base):
    __tablename__='students'
    s_id = Column(Integer,primary_key=True)
    s_name = Column(String(100))
    course = relationship('Course',secondary=stu_course)


class Course(Base):
    __tablename__='course'
    c_id = Column(Integer,primary_key=True)
    c_name = Column(String(100))
    students = relationship('Students', secondary=stu_course)

Base.metadata.create_all()

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(engine)
session = Session()

stu1 = Students(s_name='xixixi')
stu2 = Students(s_name='hahaha')

course1 = Course(c_name='PYTHON')
course2 = Course(c_name='JAVA')

stu1.course.extend([course1,course2])
stu2.course.append(course2)

session.add(stu1)
session.add(stu2)

session.commit()





